{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "inputWidgets": {},
     "nuid": "55327999-0d89-4d5b-938a-fb38bf4c9b95",
     "showTitle": false,
     "title": ""
    }
   },
   "source": [
    "# SparkSQL初体验"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "inputWidgets": {},
     "nuid": "3ee01acc-5f27-4942-bdf4-52cf95f3ccd4",
     "showTitle": false,
     "title": ""
    }
   },
   "source": [
    "## SparkCore的入口及数据抽象"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "inputWidgets": {},
     "nuid": "bb925f8b-c87e-47e4-b7b5-88d6a166497f",
     "showTitle": false,
     "title": ""
    }
   },
   "source": [
    "### SparkContext对象"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "inputWidgets": {},
     "nuid": "6cf50b54-ba5e-4834-9936-fe6f48ff81d3",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [],
   "source": [
    "sc"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "inputWidgets": {},
     "nuid": "23ed4e01-ce83-40e2-8924-cba56acf627d",
     "showTitle": false,
     "title": ""
    }
   },
   "source": [
    "### RDD数据抽象"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "inputWidgets": {},
     "nuid": "e812bbab-016b-461b-b7b4-2fdbb7aeb9ac",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [],
   "source": [
    "rdd = sc.parallelize([1,2,3,4,5,6,7,8,9])\n",
    "\n",
    "print(type(rdd))\n",
    "print(rdd.glom().collect())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "inputWidgets": {},
     "nuid": "e819daab-93c5-4621-8de1-e37f0703fdd2",
     "showTitle": false,
     "title": ""
    }
   },
   "source": [
    "## SparkSQL的入口及数据抽象"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "inputWidgets": {},
     "nuid": "20c9561c-c198-49a9-adc2-51f63a4c7b98",
     "showTitle": false,
     "title": ""
    }
   },
   "source": [
    "### SparkSession对象"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "inputWidgets": {},
     "nuid": "7b604c22-b0a4-44e2-9209-f795547c5f37",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [],
   "source": [
    "spark"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "inputWidgets": {},
     "nuid": "04005402-26d3-4a7c-86cf-48b6bd41f17e",
     "showTitle": false,
     "title": ""
    }
   },
   "source": [
    "### 从SparkSession获取SparkContext"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "inputWidgets": {},
     "nuid": "fb39446b-95d8-4d34-8e51-2229e37e70b5",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [],
   "source": [
    "spark.sparkContext"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "inputWidgets": {},
     "nuid": "b9188381-676e-4def-9823-509a35ebe944",
     "showTitle": false,
     "title": ""
    }
   },
   "source": [
    "### 使用RDD来存放数据"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "inputWidgets": {},
     "nuid": "f8ecfbc6-7a40-45ee-b178-69c90389a2b6",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [],
   "source": [
    "rdd = spark.sparkContext.textFile(\"/mnt/databrickscontainer1/restaurant-1-orders.csv\",4)\n",
    "datas = rdd.collect()\n",
    "for data in datas:\n",
    "    print(data)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "inputWidgets": {},
     "nuid": "c3103184-b4e3-4001-8e4b-2fee99332497",
     "showTitle": false,
     "title": ""
    }
   },
   "source": [
    "要取数据怎么办？"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "inputWidgets": {},
     "nuid": "5c61c948-1205-4648-b79f-42ca71010446",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [],
   "source": [
    "print(rdd.filter(lambda x: x.split(\",\")[0] == \"16115\").collect())\n",
    "print(rdd.filter(lambda x: x.split(\",\")[3] == \"25\").collect())\n",
    "print(rdd.filter(lambda x: x.split(\",\")[3] == \"25\").map(lambda x:x.split(\",\")[2]).collect())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "inputWidgets": {},
     "nuid": "146320b9-55b6-45d4-aa7a-b6e3027a037a",
     "showTitle": false,
     "title": ""
    }
   },
   "source": [
    "### 使用DataFrame来存放数据"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "inputWidgets": {},
     "nuid": "4ae0f590-2d31-404b-8df2-b67f11f01845",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [],
   "source": [
    "df = spark.read.csv(\"/mnt/databrickscontainer1/restaurant-1-orders.csv\", header=True)\n",
    "\n",
    "print(type(df))\n",
    "df.printSchema()\n",
    "df.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "inputWidgets": {},
     "nuid": "643c0ef6-3c25-4ec8-8c05-bc4c40503125",
     "showTitle": false,
     "title": ""
    }
   },
   "source": [
    "### DataFrame的API操作"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "inputWidgets": {},
     "nuid": "5f0f265a-834a-4b23-aed0-f476b36420b1",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [],
   "source": [
    "df.where(\"Quantity=5\").show()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "inputWidgets": {},
     "nuid": "c57778ce-933a-435b-aa78-ebc65921d8b8",
     "showTitle": false,
     "title": ""
    }
   },
   "source": [
    "### DataFrame的SQL操作\n",
    "\n",
    "需要先将DataFrame注册成表，给一个名称。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "inputWidgets": {},
     "nuid": "7c6fa5eb-1255-45da-89b8-86ccab0a192f",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [],
   "source": [
    "df.createOrReplaceTempView(\"restaurant_orders\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "inputWidgets": {},
     "nuid": "81e539d5-306d-4b5b-9d36-2d1254141c8e",
     "showTitle": false,
     "title": ""
    }
   },
   "source": [
    "代码中使用sql。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "inputWidgets": {},
     "nuid": "2535d3a2-8ea9-43f9-9ddb-fd23627c40cf",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [],
   "source": [
    "spark.sql(\"select * from restaurant_orders where Quantity = 10\").show()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "inputWidgets": {},
     "nuid": "232b3699-cacb-474c-8068-810895f908bc",
     "showTitle": false,
     "title": ""
    }
   },
   "source": [
    "Databricks还支持直接写SQL"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "inputWidgets": {},
     "nuid": "4d651df6-c288-4363-b35e-49a20a7a7abd",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [],
   "source": [
    "%sql\n",
    "select * from restaurant_orders where Quantity > 20"
   ]
  }
 ],
 "metadata": {
  "application/vnd.databricks.v1+notebook": {
   "dashboards": [],
   "language": "python",
   "notebookMetadata": {},
   "notebookName": "SparkSQL初体验",
   "notebookOrigID": 2180139033493910,
   "widgets": {}
  },
  "kernelspec": {
   "display_name": "",
   "name": ""
  },
  "language_info": {
   "name": ""
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}
